﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace Common
{
    public class NpoiHelper
    {
        public static List<string> GetTitles(string filename)
        {

            List<string> titles = new List<string>();
            IWorkbook wb = null;
            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) wb = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xls")) wb = new HSSFWorkbook(fs);//xls, 2003 format
                else return titles;
                for (int i = 0; i < wb.NumberOfSheets; i++)
                {
                    titles.Add(wb.GetSheetName(i));
                }
            }
            return titles;
        }

        public static DataTable ExcelToDatatable(string filename, string sheetname,int titleRowIndex=0,int startRowIndex=1, Dictionary<string, string> titles = null)
        {
            IWorkbook wb = null;
            
            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                if (filename.Contains(".xlsx")) wb = new XSSFWorkbook(fs);//xlsx 2007 Format
                else if (filename.Contains(".xls")) wb = new HSSFWorkbook(fs);//xls, 2003 format
                else return null;
                ISheet sheet = wb.GetSheet(sheetname);
                if (sheet == null) return null;
                return ExcelToDatatable(sheet, titleRowIndex, startRowIndex, titles);
            }
            
        }
        public static DataTable ExcelToDatatable( ISheet sheet, int titleRowIndex , int startRowIndex,Dictionary<string,string> titles=null )
        {
            DataTable dt = new DataTable();
            IRow titleRow = sheet.GetRow(titleRowIndex+sheet.FirstRowNum);
            //读取列标题作为datatable的column名称
            for (int i = titleRow.FirstCellNum; i < titleRow.LastCellNum; i++)
            {
                string colName = ReadCellValue(titleRow.GetCell(i)).ToString();
                if(titles!=null)
                {
                    if (titles.ContainsKey(colName))
                        colName = titles[colName];
                }
                DataColumn column = new DataColumn(colName);
                dt.Columns.Add(column);
            }
            //逐行读取数据
            for (int i = startRowIndex+sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null) continue;
                DataRow dtRow = dt.NewRow();
                for (int j = 0; j < titleRow.LastCellNum; j++)
                {
                    dtRow[j] = ReadCellValue(row.GetCell(j));
                }
                dt.Rows.Add(dtRow);
            }
            return dt;
        }
        /// <summary>
        /// 将DataTable导出为excel文件
        /// 功能：将DataTable的所有行输出到指定的文件中。如果存在标题对照字典，则用字典的value替换colname
        /// </summary>
        /// <param name="file">输出文件，xlsx格式</param>
        /// <param name="dt">要输出的datatable</param>
        /// <param name="titles">标题字典columnName-title。如果存在此词典，则标题栏替换成字典值</param>
        public static bool DataTableToExcel(string file, DataTable dt, Dictionary<string, string> titles = null)
        {
            if (dt.Rows.Count <= 0) return false;
            IWorkbook wb = new XSSFWorkbook();
            ISheet sheet = wb.CreateSheet();
            IRow titleRow = sheet.CreateRow(0);
            //写入标题行            
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                string colName = dt.Columns[i].ColumnName;
                if (titles != null && titles.ContainsKey(dt.Columns[i].ColumnName))
                {
                    colName = titles[dt.Columns[i].ColumnName];
                }
                titleRow.CreateCell(i).SetCellValue(colName);
            }
            //写入数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                wb.Write(fs);
            }
            return true;
        }
        /// <summary>
        /// 将List输出到excel文件
        /// 功能：将List的每个属性输出到指定的文件中。如果存在标题对照字典，则用字典的value替换属性名
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="file">输出文件，xlsx格式</param>
        /// <param name="list">要输出的清单</param>
        /// <param name="titles">标题字典，可选</param>
        /// <returns></returns>
        public static bool ListToExcel<T>(string file, List<T> list, Dictionary<string, string> titles = null)
        {
            if (list.Count <= 0) return false;
            IWorkbook wb = new XSSFWorkbook();
            ISheet sheet = wb.CreateSheet();
            IRow titleRow = sheet.CreateRow(0);
            var ps = list[0].GetType().GetProperties();
            //写入标题行            
            for (int i = 0; i < ps.Length; i++)
            {
                string colName = ps[i].Name;
                if (titles != null && titles.ContainsKey(ps[i].Name))
                {
                    colName = titles[ps[i].Name];
                }
                titleRow.CreateCell(i).SetCellValue(colName);
            }
            //写入数据
            for (int i = 0; i < list.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < ps.Length; j++)
                {
                    if (ps[j].GetValue(list[i], null) != null)
                    {
                        string value = ps[j].GetValue(list[i], null).ToString();
                        row.CreateCell(j).SetCellValue(value);
                    }
                }
            }

            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                wb.Write(fs);
            }

            return true;
        }

        private static object ReadCellValue(ICell cell)
        {
            object cellValue = null;
            if(cell!=null)
            {
                if (cell.CellType != CellType.String)
                    cellValue = cell.NumericCellValue;
                else
                    cellValue = cell.StringCellValue;
            }            
            return cellValue;
        }
        /// <summary>
        /// 用于将excel表格中列索引转成列号字母，从A对应0开始
        /// </summary>
        /// <param name="index">列索引,从0开始</param>
        /// <returns>列号</returns>
        public static string IndexToColumn(int index)
        {

            if (index < 0)
            {
                throw new Exception("Invalid parameter");
            }
            string column = string.Empty;
            do
            {
                if (column.Length > 0)
                {
                    index--;
                }
                column = ((char)(index % 26 + (int)'A')).ToString() + column;
                index = (int)((index - index % 26) / 26);
            } while (index > 0);
            return column;
        }
    }
}
